/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package User;

import Address.AddressBean;
import Country.CountryBean;
import Department.DepartmentBean;
import Designation.DesignationBean;
import Employee.EmployeeBean;
import State.StateBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(UserBean ub) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into users values(userid.nextval,sysdate,?,?,?,?,?,sysdate,?,?)");
            pre.setString(1, ub.getPassword());
            pre.setString(2, ub.getUserType());
            pre.setString(3, ub.getEmail());
            pre.setString(4, ub.getEmployeeName());
            pre.setDate(5, ub.getDob());
            pre.setInt(6, ub.getUserStatus());
            pre.setString(7, ub.getEntryEmail());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            return k;
        } finally {
            con.close();
        }
        return k;
    }
    public static int addStu(UserBean ub, int stuid) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into users values(userid.nextval,sysdate,?,?,(select student_email from admission where admission_id=(select ADMISSION_ID from student where student_id="+stuid+")),?,?,sysdate,?,?)");
            pre.setString(1, ub.getPassword());
            pre.setString(2, ub.getUserType());
            pre.setString(3, ub.getEmployeeName());
            pre.setDate(4, ub.getDob());
            pre.setInt(5, ub.getUserStatus());
            pre.setString(6, ub.getEntryEmail());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            return k;
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(user_id) from users");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }

        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static UserBean viewSpecific(String cnam, String val) throws SQLException {
        UserBean ub = new UserBean();
        try {

            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from users where " + cnam + "=?");
            pre.setString(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                ub.setUserId(rs.getInt(1));
                ub.setDor(rs.getDate(2));
                ub.setPassword(rs.getString(3));
                ub.setUserType(rs.getString(4));
                ub.setEmail(rs.getString(5));
                ub.setEmployeeName(rs.getString(6));
                ub.setDob(rs.getDate(7));
                ub.setEntryDate(rs.getDate(8));
                ub.setUserStatus(rs.getInt(9));
                ub.setEntryEmail(rs.getString(10));
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return ub;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from users order by user_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                UserBean ub = new UserBean();
                ub.setUserId(rs.getInt(1));
                ub.setDor(rs.getDate(2));
                ub.setPassword(rs.getString(3));
                ub.setUserType(rs.getString(4));
                ub.setEmail(rs.getString(5));
                ub.setEmployeeName(rs.getString(6));
                ub.setDob(rs.getDate(7));
                ub.setEntryDate(rs.getDate(8));
                ub.setUserStatus(rs.getInt(9));
                ub.setEntryEmail(rs.getString(10));
                list.add(ub);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    
    public static int edit(UserBean ub) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update users set dor=?, password=?, user_type=?, email=?, employee_name=?, dob=?, entrydate=sysdate, user_status=? where user_id=?");
            pre.setDate(1, ub.getDor());
            pre.setString(2, ub.getPassword());
            pre.setString(3, ub.getUserType());
            pre.setString(4, ub.getEmail());
            pre.setString(5, ub.getEmployeeName());
            pre.setDate(6, ub.getDob());
            pre.setInt(7, ub.getUserStatus());
            pre.setInt(8, ub.getUserId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from users where user_id=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static String viewName(int id) throws SQLException {
        String name = "";
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select EMPLOYEE_NAME from USERS where USER_ID=?");
            pre.setInt(1, id);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                name = rs.getString(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return name;
    }

    public static UserBean viewSpecific(int val) throws SQLException {
        EmployeeBean eb = new EmployeeBean();
        AddressBean ab = new AddressBean();
        StateBean sb = new StateBean();
        CountryBean cb = new CountryBean();
        DepartmentBean db = new DepartmentBean();
        DesignationBean dib = new DesignationBean();
        UserBean ub = new UserBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from users u left join employee e on u.user_id=e.user_id left join address a on e.address_id=a.address_id left join country c on a.country_id=c.country_id left join state s on a.state_id=s.state_id left join department d on e.department_id=d.department_id left join designation de on e.designation_id=de.designation_id  where u.user_id=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                ub.setUserId(rs.getInt(1));
                ub.setDor(rs.getDate(2));
                ub.setPassword(rs.getString(3));
                ub.setUserType(rs.getString(4));
                ub.setEmail(rs.getString(5));
                ub.setEmployeeName(rs.getString(6));
                ub.setDob(rs.getDate(7));
                ub.setEntryDate(rs.getDate(8));
                ub.setUserStatus(rs.getInt(9));
                ub.setEntryEmail(rs.getString(10));

                eb.setEmployeeId(rs.getInt(11));
                eb.setEmployeeFatherName(rs.getString(12));
                eb.setAddressId(rs.getInt(13));
                eb.setPanCard(rs.getString(14));
                eb.setMobile(rs.getString(15));
                eb.setPhone(rs.getString(16));
                eb.setGender(rs.getString(17));
                eb.setHigherQuali(rs.getString(18));
                eb.setDoj(rs.getDate(19));
                eb.setJobdetails(rs.getString(20));
                eb.setDepartmentId(rs.getInt(21));
                eb.setDesignationId(rs.getInt(22));
                eb.setUserId(rs.getInt(23));
                eb.setPhoto(rs.getString(24));
                eb.setEntryDate(rs.getDate(25));
                eb.setEmployeeType(rs.getInt(26));
                eb.setEmployeeStatus(rs.getInt(27));

                ab.setAddressId(rs.getInt(28));
                ab.setAddress1(rs.getString(29));
                ab.setAddress2(rs.getString(30));
                ab.setCountryId(rs.getInt(31));
                ab.setStateId(rs.getInt(32));
                ab.setPinCode(rs.getString(33));
                ab.setEntryDate(rs.getDate(34));

                cb.setCountryId(rs.getInt(35));
                cb.setCountryName(rs.getString(36));
                cb.setEntryDate(rs.getDate(37));
                ab.setCountryBean(cb);

                sb.setStateId(rs.getInt(38));
                sb.setCountryId(rs.getInt(39));
                sb.setStateName(rs.getString(40));
                sb.setEntryDate(rs.getDate(41));
                ab.setStateBean(sb);

                eb.setAddressBean(ab);

                db.setDepartmentId(rs.getInt(42));
                db.setDepartmentName(rs.getString(43));
                db.setEntryDate(rs.getDate(44));
                eb.setDepartmentBean(db);

                dib.setDesignationId(rs.getInt(45));
                dib.setDesignation(rs.getString(46));
                dib.setEntryDate(rs.getDate(47));
                eb.setDesignationBean(dib);
                ub.setEmployeeBean(eb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return ub;
    }
    public static int matchpass(String pass, int uid)throws SQLException{
        int val=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select password from users where USER_ID=? and PASSWORD=?");
            pre.setInt(1, uid);
            pre.setString(2, pass);
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                if(rs.getString(1).length()!=0){
                    val=1;
                }else{
                    val=0;
                }
            }
        }catch(Exception e){
            return val;
        }finally{
            con.close();
        }return val;
    }
    public static int changepass(int uid, String Pass, String oldPass)throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("update users set password=? where user_id=? and PASSWORD=?");
            pre.setString(1, Pass);
            pre.setInt(2, uid);
            pre.setString(3, oldPass);
            i=pre.executeUpdate();
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }
}
